﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using DeansWsccProject.App_Code;

namespace DeansWsccProject
{
    public partial class details : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack) // Do this when navigating to this page from elsewhere
            {
                if (Request.Params["ID"] == null) { Response.Redirect("list.aspx"); }

                populateEBookDetailsGridView();
                populateReviewsAccordion();
                populateTagsCheckBoxList();
            }

            // Don't display Reviews Email textbox if user is already logged in (will use their username automatically instead)
            if (User.Identity.IsAuthenticated)
            {
                phTbReviewerEmail.Visible = false;
            }
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            insertReviewIntoDatabase();
            updateEBookAvgRatingsFieldInEBooksTable();
            InsertNewTagsIntoTagTables(); // tags typed into textbox by user inserted into tag table and junction table
            InsertExistingTagsIntoJunctionTable(); // tags checked in checkboxes inserted into junction table

            populateEBookDetailsGridView();
            populateReviewsAccordion();
            populateTagsCheckBoxList();

            // Remove review and tag data from text boxes
            tbReviewTitle.Text = "";
            tbReview.Text = "";
            tbNewEBookTags.Text = "";
        }

        private void populateEBookDetailsGridView()
        {
            functions.fillDataView(eBookDetailsGridView, "SELECT [ID], [eBookTitle], [eBookImage], [eBookReleaseDate], [eBookDescription], [eBookDownloadUrl], [eBookRating] FROM [eBooks] WHERE ([ID] = ?) ORDER BY [eBookReleaseDate]", Request.Params["ID"]);
        }

        private void populateReviewsAccordion()
        {
            functions.fillAccordion(eBookReviewsAccordion, "SELECT * FROM reviews WHERE eBook_fkid = @f1 ORDER BY [reviewDate] DESC", Request.Params["ID"]);
            if (functions.getDataFromTable("SELECT TOP 1 ID FROM reviews WHERE eBook_fkid = @f1", Request.Params["ID"])[0] == "")
            {
                recentReviewsPlaceholder.Visible = false;
            }
        }

        private void populateTagsCheckBoxList()
        {
            string[] tags = functions.getDataFromTable("SELECT tag FROM tags ORDER BY tag", null);
            cblEBookTags.Items.Clear();
            foreach (string tag in tags)
            {
                cblEBookTags.Items.Add(tag);
            }
        }

        protected string getEBookDownloadLink(string url, string authenticatedUserStr, string unauthenticatedUserStr)
        {
            if (User.Identity.IsAuthenticated) { return "<a href='" + url + "'>" + authenticatedUserStr + "</a>"; }
            else { return "<a href='/Account/Login.aspx?ReturnUrl=%2f" + url + "'>" + unauthenticatedUserStr + "</a>"; } // temp messy link to work around public visibility of ebooks!
        }

        protected string getReviewerLink(string reviewer)
        {
            if (reviewer.Contains('@'))
            {
                return "<a href='mailto:" + reviewer + "'>" + reviewer + "</a>";
            }
            else
            {
                return "<a href='/profile.aspx?profileUserName=" + reviewer + "'>" + reviewer + "</a>";
            }
        }

        private void insertReviewIntoDatabase()
        {
            object[] parameters = { null, tbReviewTitle.Text, tbReview.Text, ajtkReviewRating.CurrentRating * 10, Request.Params["ID"], DateTime.Now.Date }; // rating * 10 so as to store rating in database as a mark out of 100 (makes gridview sorting more accurate)
            if (User.Identity.IsAuthenticated)
            {
                parameters[0] = User.Identity.Name; // uses username of logged in user automatically for inserting into database ( @f1 param -> reviewer field )
            }
            else
            {
                parameters[0] = tbReviewerEmail.Text; // user manually enters an email address which is then inserted into database ( @f1 param -> reviewer field )
            }
            functions.putDataInTable("INSERT INTO reviews(reviewer,title,review,rating,eBook_fkid,reviewDate) VALUES(@f1,@f2,@f3,@f4,@f5,@f6)", parameters);
        }

        private void updateEBookAvgRatingsFieldInEBooksTable()
        {
            object[] parameters = { functions.getDataFromTable("SELECT avgRating FROM eBookAverageRatings WHERE eBook_fkid = @f1", Request.Params["ID"])[0], Request.Params["ID"] };
            functions.putDataInTable("UPDATE eBooks SET eBookRating = @f1 WHERE ID = @f2", parameters);
        }

        private void InsertNewTagsIntoTagTables()
        {
            OleDbConnection oleDbConn = functions.getDbConnection();
            oleDbConn.Open();

            if (tbNewEBookTags.Text.Trim() != "") // do this if new tags are entered by user
            {
                string[] newTags = tbNewEBookTags.Text.Split(',');

                foreach (string newTag in newTags)
                {
                    if (newTag.Trim() != "") // ignore empty arrays ( i.e. if user typed ", ," )
                    {
                        // Insert new tags into tag table
                        OleDbCommand cmdJoin = new OleDbCommand("INSERT INTO tags(tag) VALUES(@f1)", oleDbConn);
                        cmdJoin.CommandType = CommandType.Text;
                        cmdJoin.Parameters.AddWithValue("@f1", newTag.Trim());
                        cmdJoin.ExecuteNonQuery();
                        cmdJoin.Dispose();

                        // find id of each new tag
                        OleDbCommand cmdFind = new OleDbCommand("SELECT TOP 1 ID FROM tags ORDER BY ID DESC", oleDbConn);
                        OleDbDataReader dataReader = cmdFind.ExecuteReader();
                        dataReader.Read();

                        // associate tag ID with ebook ID in the junction table
                        string lastId = (dataReader.GetValue(0)).ToString();
                        dataReader.Dispose();
                        cmdFind.Dispose();
                        object[] tagJuncParams = { Request.Params["ID"], lastId };
                        functions.putDataInTable("INSERT INTO eBookTagJunction(eBook_fkid, tag_fkid) VALUES(@f1,@f2)", tagJuncParams);
                    }
                }
            }
            oleDbConn.Close();
            oleDbConn.Dispose();
        }

        private void InsertExistingTagsIntoJunctionTable()
        {
            foreach (ListItem cb in cblEBookTags.Items)
            {
                if (cb.Selected)
                {
                    object[] parameters = { Request.Params["ID"], functions.getDataFromTable("SELECT ID FROM tags WHERE tag = @f1", cb.Value)[0] };
                    functions.putDataInTable("INSERT INTO eBookTagJunction(eBook_fkid, tag_fkid) VALUES(@f1,@f2)", parameters);
                }
            }
        }
    }
}